﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using EnterpriseFund.Common;
using System.Data;
using DevExpress.Web.Mvc;

namespace EnterpriseFund.Controllers
{
       [OutputCache(Duration = 0, VaryByParam = "none")]
    public class QueryTBController : AdminBaseController
    {
        //
        // GET: /QueryTB/
           [AuthorityActionFilter("m_QueryTB", Order = 1)]
           [LogPageActionFilter("重点税源税收情况表", Order = 2)]
        public ActionResult Index()
        {
            BLL.Common commonBLL = new BLL.Common();
            DataSet yds = commonBLL.GetYear();
            List<SelectListItem> yearList = new List<SelectListItem>();
            for (int i = 0; i < yds.Tables[0].Rows.Count; i++)
            {
                yearList.Add(new SelectListItem() { Text = yds.Tables[0].Rows[i]["FYear"].ToString(), Value = yds.Tables[0].Rows[i]["FYear"].ToString() });
            }

            ViewData["YearData"] = yearList;
            string year = OperateSession.get(GlobalConstant.PARAM_SYSYEAR);
            ViewData["year"] = year;
            return View();
        }
        public ActionResult QueryTBSummarySheet(string Year, string Mon)
        {
            BLL.SB_ZSXX SB_ZSXXBLL = new BLL.SB_ZSXX();
            string dtime = Year.ToString() + Mon.ToString().PadLeft(2,'0') + "01";
            DataTable dt = SB_ZSXXBLL.GetQueryTB(dtime, "");
            ViewData["year"] = Year;
            ViewData["foreyear"] = (int.Parse(Year) - 1).ToString();
            ViewData["mon"] = int.Parse(Mon);
            ViewData["showData"] = GetData(dt);
            return View("QueryTBSummarySheet");

        }
        public DataTable GetData(DataTable newDt)
        {
            newDt.Columns.Remove("NSRBM");
            newDt.Columns.Remove("NSRDZDAH");
            newDt.Columns.Remove("NSRSBH");
            for (int i = 0; i < newDt.Rows.Count; i++)
            {


            foreach (DataColumn dc in newDt.Columns)
            {
                if (newDt.Rows[i][dc.ColumnName]!=null)
                {
               
                    if (dc.DataType == typeof(decimal) & dc.ColumnName.Contains("tb") & dc.ColumnName != "NSRDZDAH"&string.IsNullOrEmpty(newDt.Rows[i][dc.ColumnName].ToString()))
                    {
                        newDt.Rows[i][dc.ColumnName] = 0;
                    }
                }
                if (dc.DataType == typeof(decimal) & !dc.ColumnName.Contains("tb") & dc.ColumnName != "NSRDZDAH")
                {
                    newDt.Rows[i][dc.ColumnName]  =decimal.Round((decimal.Parse(newDt.Rows[i][dc.ColumnName].ToString())/10000),2);
                }
                
            }

            }
            DataRow hjdr = newDt.NewRow();
            foreach (DataColumn dc in newDt.Columns)
            {
                if (dc.DataType == typeof(decimal) & !dc.ColumnName.Contains("tb"))
                {
                    hjdr[dc.ColumnName] = newDt.Compute("sum([" + dc.ColumnName + "])", "");
                }
                if (dc.ColumnName == "NSRMC")
                {
                    hjdr["NSRMC"] = "合计";
                }
            }
            hjdr["montb1"] = decimal.Parse(hjdr["se3"].ToString()) != 0 ? (int)Math.Round(((decimal.Parse(hjdr["se1"].ToString()) - decimal.Parse(hjdr["se3"].ToString())) / decimal.Parse(hjdr["se3"].ToString()) * 100), 0) : 0;
            hjdr["yrtb1"] = decimal.Parse(hjdr["se4"].ToString()) != 0 ? (int)Math.Round(((decimal.Parse(hjdr["se2"].ToString()) - decimal.Parse(hjdr["se4"].ToString())) / decimal.Parse(hjdr["se4"].ToString()) * 100), 0) : 0;

            hjdr["montba1"] = decimal.Parse(hjdr["sea3"].ToString()) != 0 ? (int)Math.Round(((decimal.Parse(hjdr["sea1"].ToString()) - decimal.Parse(hjdr["sea3"].ToString())) / decimal.Parse(hjdr["sea3"].ToString()) * 100), 0) : 0;
            hjdr["yrtba1"] = decimal.Parse(hjdr["sea4"].ToString()) != 0 ? (int)Math.Round(((decimal.Parse(hjdr["sea2"].ToString()) - decimal.Parse(hjdr["sea4"].ToString())) / decimal.Parse(hjdr["sea4"].ToString()) * 100), 0) : 0;

            hjdr["montbb1"] = decimal.Parse(hjdr["seb3"].ToString()) != 0 ? (int)Math.Round(((decimal.Parse(hjdr["seb1"].ToString()) - decimal.Parse(hjdr["seb3"].ToString())) / decimal.Parse(hjdr["seb3"].ToString()) * 100), 0) : 0;
            hjdr["yrtbb1"] = decimal.Parse(hjdr["seb4"].ToString()) != 0 ? (int)Math.Round(((decimal.Parse(hjdr["seb2"].ToString()) - decimal.Parse(hjdr["seb4"].ToString())) / decimal.Parse(hjdr["seb4"].ToString()) * 100), 0) : 0;

            hjdr["montbc1"] = decimal.Parse(hjdr["sec3"].ToString()) != 0 ? (int)Math.Round(((decimal.Parse(hjdr["sec1"].ToString()) - decimal.Parse(hjdr["sec3"].ToString())) / decimal.Parse(hjdr["sec3"].ToString()) * 100), 0) : 0;
            hjdr["yrtbc1"] = decimal.Parse(hjdr["sec4"].ToString()) != 0 ? (int)Math.Round(((decimal.Parse(hjdr["sec2"].ToString()) - decimal.Parse(hjdr["sec4"].ToString())) / decimal.Parse(hjdr["sec4"].ToString()) * 100), 0) : 0;

            hjdr["montbd1"] = decimal.Parse(hjdr["sed3"].ToString()) != 0 ? (int)Math.Round(((decimal.Parse(hjdr["sed1"].ToString()) - decimal.Parse(hjdr["sed3"].ToString())) / decimal.Parse(hjdr["sed3"].ToString()) * 100), 0) : 0;
            hjdr["yrtbd1"] = decimal.Parse(hjdr["sed4"].ToString()) != 0 ? (int)Math.Round(((decimal.Parse(hjdr["sed2"].ToString()) - decimal.Parse(hjdr["sed4"].ToString())) / decimal.Parse(hjdr["sed4"].ToString())*100), 0) : 0;

            hjdr["montbe1"] = decimal.Parse(hjdr["see3"].ToString()) != 0 ? (int)Math.Round(((decimal.Parse(hjdr["see1"].ToString()) - decimal.Parse(hjdr["see3"].ToString())) / decimal.Parse(hjdr["see3"].ToString()) * 100), 0) : 0;
            hjdr["yrtbe1"] = decimal.Parse(hjdr["see4"].ToString()) != 0 ? (int)Math.Round(((decimal.Parse(hjdr["see2"].ToString()) - decimal.Parse(hjdr["see4"].ToString())) / decimal.Parse(hjdr["see4"].ToString()) * 100), 0) : 0;

            hjdr["montbf1"] = decimal.Parse(hjdr["sef3"].ToString()) != 0 ? (int)Math.Round(((decimal.Parse(hjdr["sef1"].ToString()) - decimal.Parse(hjdr["sef3"].ToString())) / decimal.Parse(hjdr["sef3"].ToString()) * 100), 0) : 0;
            hjdr["yrtbf1"] = decimal.Parse(hjdr["sef4"].ToString()) != 0 ? (int)Math.Round(((decimal.Parse(hjdr["sef2"].ToString()) - decimal.Parse(hjdr["sef4"].ToString())) / decimal.Parse(hjdr["sef4"].ToString()) * 100), 0) : 0;






            newDt.Rows.Add(hjdr);
           
            return newDt;
        }
        public System.Web.Mvc.FileResult ToExecl()
        {
            try
            {
                BLL.SB_ZSXX SB_ZSXXBLL = new BLL.SB_ZSXX();
                //string Year = ComboBoxExtension.GetValue<string>("Fyear");
                //string Mon = ComboBoxExtension.GetValue<string>("Fmon");
                //根据隐藏字段
                string Year = Fetch.Post("hFyear");
                string Mon = Fetch.Post("hFmon");
                string dtime = Year.ToString() + Mon.ToString().PadLeft(2, '0') + "01";
                DataTable showData = GetData(SB_ZSXXBLL.GetQueryTB(dtime, ""));
                int totalRowCount = 1;
                List<Model.XlsModel> xlsModelList = new List<Model.XlsModel>();
                Model.XlsModel xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 44;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "单位：万元";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Right;
                xlsModelList.Add(xlsModel);
                totalRowCount++;

                xlsModel = new Model.XlsModel();
                xlsModel.RowSpan = 3;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "序号";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.RowSpan = 3;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "企业名称";

                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 6;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "合计";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 6;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "增值税";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);


                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 6;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "消费税";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);


                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 6;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "营业税";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 6;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "企业所得税";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 6;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "个人所得税";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 6;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "其他税收";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
                totalRowCount++;

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = Year;
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = (int.Parse(Year) - 1).ToString();
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "同比增减";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = Year;
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = (int.Parse(Year) - 1).ToString();
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "同比增减";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = Year;
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = (int.Parse(Year) - 1).ToString();
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "同比增减";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = Year;
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = (int.Parse(Year) - 1).ToString();
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "同比增减";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = Year;
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = (int.Parse(Year) - 1).ToString();
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "同比增减";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = Year;
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = (int.Parse(Year) - 1).ToString();
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "同比增减";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = Year;
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = (int.Parse(Year) - 1).ToString();
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "同比增减";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                totalRowCount++;

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "1-" + int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "1-" + int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "1-" + int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);


                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "1-" + int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "1-" + int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "1-" + int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);



                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "1-" + int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "1-" + int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "1-" + int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);



                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "1-" + int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "1-" + int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "1-" + int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);


                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "1-" + int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "1-" + int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "1-" + int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);


                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "1-" + int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "1-" + int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "1-" + int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);



                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "1-" + int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "1-" + int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "1-" + int.Parse(Mon) + "月";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);



                for (int i = 0; i < showData.Rows.Count; i++)
                {
                    totalRowCount++;
                    if (showData.Rows[i]["NSRMC"] == "合计")
                    {

                        xlsModel = new Model.XlsModel();
                        xlsModel.ColSpan = 2;
                        xlsModel.RowIndex = totalRowCount;
                        xlsModel.Title = "合计";
                        xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                        xlsModelList.Add(xlsModel);
                        foreach (System.Data.DataColumn dc in showData.Columns)
                        {
                            if (showData.Rows[i][dc.ColumnName].ToString() != "合计")
                            {
                                if (!string.IsNullOrEmpty(showData.Rows[i][dc.ColumnName].ToString()))
                                {
                                    xlsModel = new Model.XlsModel();
                                    xlsModel.ColSpan = 1;
                                    xlsModel.RowIndex = totalRowCount;
                                    xlsModel.Title = showData.Rows[i][dc.ColumnName].ToString();
                                    xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                                    xlsModelList.Add(xlsModel);
                                }
                                else
                                {
                                    xlsModel = new Model.XlsModel();
                                    xlsModel.ColSpan = 1;
                                    xlsModel.RowIndex = totalRowCount;
                                    xlsModel.Title = "";
                                    xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                                    xlsModelList.Add(xlsModel);
                                }
                            }
                        }
                    }
                    else
                    {
                        xlsModel = new Model.XlsModel();
                        xlsModel.ColSpan = 1;
                        xlsModel.RowIndex = totalRowCount;
                        xlsModel.Title = (totalRowCount - 4).ToString();
                        xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                        xlsModelList.Add(xlsModel);
                        foreach (System.Data.DataColumn dc in showData.Columns)
                        {
                            if (!string.IsNullOrEmpty(showData.Rows[i][dc.ColumnName].ToString()))
                            {
                                if (showData.Rows[i][dc.ColumnName].ToString().Contains("-") & dc.ColumnName.Contains("tb"))
                                {
                                    //xlsModel = new Model.XlsModel();
                                    //xlsModel.ColSpan = 1;
                                    //xlsModel.RowIndex = totalRowCount;
                                    //xlsModel.Title = "(" + showData.Rows[i][dc.ColumnName].ToString().Substring(1) + ")";
                                    //xlsModel.PColor = Model.XlsModel.PatternColor.Red;
                                    // xlsModel.UserPatternColor = true;
                                    //xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                                    //xlsModelList.Add(xlsModel);

                                    xlsModel = new Model.XlsModel();
                                    xlsModel.ColSpan = 1;
                                    xlsModel.RowIndex = totalRowCount;
                                    xlsModel.Title = showData.Rows[i][dc.ColumnName].ToString();

                                    xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                                    xlsModelList.Add(xlsModel);
                                }
                                else
                                {
                                    xlsModel = new Model.XlsModel();
                                    xlsModel.ColSpan = 1;
                                    xlsModel.RowIndex = totalRowCount;
                                    xlsModel.Title = showData.Rows[i][dc.ColumnName].ToString();

                                    xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                                    xlsModelList.Add(xlsModel);
                                }
                            }
                            else
                            {
                                xlsModel = new Model.XlsModel();
                                xlsModel.ColSpan = 1;
                                xlsModel.RowIndex = totalRowCount;
                                xlsModel.Title = "";
                                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                                xlsModelList.Add(xlsModel);
                            }
                        }

                    }



                }
                List<Model.XlsModel_Column> xlsModelColumns = new List<Model.XlsModel_Column>();
                xlsModelColumns.Add(new Model.XlsModel_Column()
                {
                    ColumnIndexStart = 0,
                    ColumnIndexEnd = 1,
                    Width = 10
                });
                xlsModelColumns.Add(new Model.XlsModel_Column()
                {
                    ColumnIndexStart = 1,
                    ColumnIndexEnd = 2,
                    Width = 45
                });
                xlsModelColumns.Add(new Model.XlsModel_Column()
                {
                    ColumnIndexStart = 2,
                    ColumnIndexEnd = 44,
                    Width = 15
                });
                List<Model.XlsModel_Row> xlsModelRows = new List<Model.XlsModel_Row>();
                ushort rowCount = (ushort)(totalRowCount);
                xlsModelRows.Add(new Model.XlsModel_Row()
                {
                    RowIndexStart = 1,
                    RowIndexEnd = 1,
                    RowHeight = 23
                });
                xlsModelRows.Add(new Model.XlsModel_Row()
                {
                    RowIndexStart = 2,
                    RowIndexEnd = rowCount,
                    RowHeight = 15

                });
                Model.ExcelTitleFont excelTitleFont = new Model.ExcelTitleFont()
                {
                    FontName = "宋体",
                    FontSize = 18,
                    Bold = true
                };
                byte[] b = Common.ExcelClass.ExportToExcelReturnByte("", Year + "年" + int.Parse(Mon) + "月份大榭开发区重点税源企业税收收入情况表", Year + "年" + int.Parse(Mon) + "月份大榭开发区重点税源企业税收收入情况表", excelTitleFont, totalRowCount, xlsModelList, xlsModelColumns, xlsModelRows);

                string name = Year + "年" + int.Parse(Mon) + "月份大榭开发区重点税源企业税收收入情况表.xls";
                return File(b, "application/vnd.ms-excel", HttpUtility.UrlEncode(name));
            }
            catch (System.Exception ex)
            {

                return null;
            }


        }
    }
}
